<?php

$roleIds = array(1=>'lice',2=>'ant',3=>'wasp',4=>'bumble bee');

class OTR{
	var $dbErrorMsg = "Failed retreiving data from database: ";
	function __construct(){
		$server = "localhost"; 	//SERVER: localhost
		$db = "proorg";			//SERVER: ontheblue_se
		$dbu = "root";			//SERVER: ontheblue_se
		$dbp = "";				//SERVER: secret
		mysql_connect($server, $dbu, $dbp) or die("Failed connecting to server ".$server);
		mysql_select_db($db) or die("Failed selecting database: ".$db);
		session_start();
	}

	function validateLogin($username, $password){
		$sql = "Select * From `users` Where `email`='".$username."'";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		if(mysql_num_rows($res)==0){
			return array("result"=>"FALSE", "msg"=>"User not found in database");
			exit();
		}
		$rs = mysql_fetch_assoc($res);
		if($rs['password']!=$password){
			return array("result"=>"FALSE", "msg"=>"Wrong password");
			exit();
		}
		$_SESSION['auth']=true;
		$_SESSION['id']=$rs['id'];
		$_SESSION['initials']=$rs['initials'];
		return array("result"=>"SUCCESS", "");
	}
	
	function getUser(){
		return array("id"=>$_SESSION['id'], "initials"=>$_SESSION['id']);
	}
	function getUsers(){
		$data = array();
		$sql = "Select *, CONCAT(`firstname`, `lastname`) as `fullname` From `users` Order by `firstname`, `lastname`";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		while($row=mysql_fetch_assoc($res)){
			array_push($data, $row);
		}
		return $data;
	}

	function getParticipants($id){
		$data = array();
		$sql = "Select *, CONCAT(`firstname`, ' ', `lastname`) as `fullname` From `users` Left Join `participants` On `users`.`id` = `user_id` And `project_id`='".$id."'";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		while($row=mysql_fetch_assoc($res)){
			array_push($data, $row);
		}
		return $data;
	}

	function checkLogin(){
		if(isset($_SESSION['auth'])){
			return true;
		} else {
			return false;
		}
		
	}
	
	function getActiveProjects(){
		$data = array();
		$sql = "Select * From `projects` Where `completed`=0";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		while($row=mysql_fetch_assoc($res)){
			array_push($data, $row);
		}
		return $data;
	}

	function getProjects(){
		$data = array();
		$sql = "Select * From `projects`";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		while($row=mysql_fetch_assoc($res)){
			array_push($data, $row);
		}
		return $data;
	}
	
	function getProject($id){
		$data = array();
		$sql = "Select * From `projects` Where `id`=".$id;
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		return mysql_fetch_assoc($res);
	}
	
	function setProject($project_id, $project_shortname, $project_description, $project_local, $project_site, $project_customer, $project_started, $project_ended, $project_completed){
		if($project_id=="*NEW*"){
			$sql = "Insert into `projects` (`shortname`, `description`, `local`, `site`, `customer_id`, `started_date`, `completed_date`, `completed`)
					Values ('".$project_shortname."', 
							'".$project_description."', 
							'".$project_local."', 
							'".$project_site."', 
							'".$project_customer."', 
							'".$project_started."', 
							'".$project_ended."', 
							'".$project_completed."')";
		} else {
			$sql = "Update `projects`
					Set
						`shortname`='".$project_shortname."', 
						`description`='".$project_description."', 
						`local`='".$project_local."', 
						`site`='".$project_site."', 
						`customer_id`='".$project_customer."', 
						`started_date`='".$project_started."', 
						`completed_date`='".$project_ended."', 
						`completed`='".$project_completed."'
					Where `id`=".$project_id;
		}
		mysql_query($sql) or die($this->dbErrorMsg.$sql);
		return "Successfully saved!";
	}
	
	function getCustomers(){
		$data = array();
		$sql = "Select `id`, `company` From `customers`";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		while($row=mysql_fetch_assoc($res)){
			array_push($data, $row);
		}
		return $data;
	}
	
	function getCustomer($id){
		$data = array();
		$sql = "Select * From `customers` Where `id`=".$id;
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		return mysql_fetch_assoc($res);
	}

	function setCustomer($id, $company, $contact, $phone, $email){
		if($id=="*NEW*"){
			$sql = "Insert into `customers` (`company`, `contact`, `phone`, `email`)
					Values ('".$company."', 
							'".$contact."', 
							'".$phone."', 
							'".$email."')";
		} else {
			$sql = "Update `customers`
					Set
						`company`='".$company."', 
						`contact`='".$contact."', 
						`phone`='".$phone."', 
						`email`='".$email."'
					Where `id`=".$id;
		}
		mysql_query($sql) or die($this->dbErrorMsg.$sql);
		return "Successfully saved!";
	}

	function getTasks($projectid){
		$data = array();
		/*$sql = "Select `tasks`.`id`, `tasks`.`owner_id`, `tasks`.`task`, `tasks`.`prio`, `tasks`.`bit_completed`, `tasks`.`completed_on`, `tasks`.`ordinal`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`punch_out`, `punch_in`)))) as `time` 
				From `tasks`
				Left Join `punchclock`
				On `punchclock`.`related_id` = `tasks`.`id`
				And `punchclock`.`related_table`='tasks'
				Where `tasks`.`related_id`=".$projectid."
				And `tasks`.`related_table` = 'projects'
				Group by `tasks`.`id`, `tasks`.`owner_id`, `tasks`.`task`, `tasks`.`prio`, `tasks`.`bit_completed`, `tasks`.`completed_on`, `tasks`.`ordinal`
				Order by `bit_completed`, `prio` desc, `task`";*/
		$sql = "Select `tasks`.`id`, `tasks`.`owner_id`, `tasks`.`task`, `tasks`.`prio`, `tasks`.`bit_completed`, `tasks`.`completed_on`, `tasks`.`ordinal`,
				(
					Select SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`punch_out`, `punch_in`)))) as `time`
					From `punchclock` 
					Where `related_id`=`tasks`.`id`
					And `related_table`='tasks'				
				) as `time`
				From `tasks`
				Where `tasks`.`related_id`=".$projectid."
				And `tasks`.`related_table` = 'projects'
				Order by `bit_completed`, `prio` desc, `task`";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		while($row=mysql_fetch_assoc($res)){
			array_push($data, $row);
		}
		return $data;
	}

	function getSubTasks($taskid){
		$data = array();
		/*$sql = "Select `tasks`.`id`, `tasks`.`owner_id`, `tasks`.`task`, `tasks`.`prio`, `tasks`.`bit_completed`, `tasks`.`completed_on`, `tasks`.`ordinal`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`punch_out`, `punch_in`)))) as `time` 
				From `tasks`
				Left Join `punchclock`
				On `punchclock`.`related_id` = `tasks`.`id`
				And `punchclock`.`related_table`='tasks'
				Where `tasks`.`related_id`=".$taskid."
				And `tasks`.`related_table` = 'tasks'
				Group by `tasks`.`id`
				Order by `completed_on`, `prio` desc";*/
		$sql = "Select `tasks`.`id`, `tasks`.`owner_id`, `tasks`.`task`, `tasks`.`prio`, `tasks`.`bit_completed`, `tasks`.`completed_on`, `tasks`.`ordinal`,
				(
					Select SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`punch_out`, `punch_in`)))) as `time`
					From `punchclock` 
					Where `related_id`=`tasks`.`id`
					And `related_table`='tasks'
				) as `time`
				From `tasks`
				Where `tasks`.`related_id`=".$taskid."
				And `tasks`.`related_table` = 'tasks'
				Order by `completed_on`, `prio` desc";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		while($row=mysql_fetch_assoc($res)){
			array_push($data, $row);
		}
		return $data;
		
	}
	
	function getPunchTime($taskid, $userid){
		$sql = "Select timediff(`punch_out`, `punch_in`) as `time`
				From `punchclock` 
				Where `related_id`=".$taskid."
				And `user_id`=".$userid."
				And `related_table`='tasks'
				Order by `id` desc
				Limit 1";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		$row=mysql_fetch_assoc($res);
		return $row['time'];
	}
	
	function getTaskTotalTime($taskid){
		$sql = "Select SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`punchclock`.`punch_out`, `punchclock`.`punch_in`)))
				+ 
				(
					Select SUM(TIME_TO_SEC(TIMEDIFF(`punchclock`.`punch_out`, `punchclock`.`punch_in`)))
					From `tasks` as tM
					Inner Join `tasks` as tS
					On `tM`.`id` = `tS`.`related_id`
					And tS.related_table = 'tasks'
					Inner Join `punchclock`
					On `tS`.id = `punchclock`.`related_id`
					And `punchclock`.`related_table` = 'tasks'
					Where tM.id = ".$taskid."
				)) as `time`
				From `punchclock`
				Where `related_id` = ".$taskid."
				And `related_table` = 'tasks'";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		$row=mysql_fetch_assoc($res);
		$main = $row['time'];
		return $main;
	}
	
	function getProjectTimeTotal($id){
		//tasks and subtasks
		$sql = "Select 
				SEC_TO_TIME(
					SUM(TIME_TO_SEC(TIMEDIFF(`punchclock`.`punch_out`, `punchclock`.`punch_in`)))
					+
					(
						Select SUM(TIME_TO_SEC(TIMEDIFF(`punchclock`.`punch_out`, `punchclock`.`punch_in`)))
						From `tasks` as `t`
						Inner Join `tasks` as `st`
						On `t`.`id` = `st`.`related_id`
						And `st`.`related_table` = 'tasks'
						Inner Join `punchclock`
						On `st`.`id` = `punchclock`.`related_id`
						And `punchclock`.`related_table` = 'tasks'
						Where `t`.`related_id` = 3
						And `t`.`related_table` = 'projects'
					)
				) as `time` 
				From `tasks`
				Inner Join `punchclock`
				On `punchclock`.`related_id` = `tasks`.`id`
				And `punchclock`.`related_table` = 'tasks'
				Where `tasks`.`related_id`=".$id."
				And `tasks`.`related_table` = 'projects'
				Group by `tasks`.`related_id`";
		$res = mysql_query($sql) or die($this->dbErrorMsg.$sql);
		$row=mysql_fetch_assoc($res);
		return $row['time'];
	}
		
	function getPrioCSS($lvl, $done){
		if($done){
			return 'prio_completed'; 
		};
		switch($lvl){
			case 1:
				return 'prio_low';
			break;
			case 2:
				return 'prio_medium';
			break;
			case 3:
				return 'prio_high';
			break;
			case 4:
				return 'prio_mad';
			break;
		}
		
	}
}
?>